import json
import pymysql
from typing import Dict, List, Optional

class GroupConfigManager:
    def __init__(self, db_config_path: str):
        """
        初始化数据库连接
        
        Args:
            db_config_path: 数据库配置文件路径
        """
        self.conn = self._connect_db(db_config_path)
        self.cursor = self.conn.cursor()
        
    def _connect_db(self, db_config_path: str) -> pymysql.connections.Connection:
        """
        连接数据库
        
        Args:
            db_config_path: 数据库配置文件路径
            
        Returns:
            pymysql连接对象
        """
        with open(db_config_path, 'r') as file:  
            db_config = json.load(file)
            
        conn = pymysql.connect(
            host=db_config["dbConnectConfig"]["host"],
            user=db_config["dbConnectConfig"]["user"],
            passwd=db_config["dbConnectConfig"]["passwd"],
            port=db_config["dbConnectConfig"]["port"],
            db=db_config["dbConnectConfig"]["db"],
            charset=db_config["dbConnectConfig"]["charset"]
        )
        return conn
    
    def initialize_table(self) -> None:
        """
        初始化数据库表
        """
        create_table = """
        CREATE TABLE IF NOT EXISTS group_config (
            id INT AUTO_INCREMENT PRIMARY KEY,
            group_code VARCHAR(20) NOT NULL UNIQUE,
            json_file VARCHAR(50) NOT NULL,
            group_name VARCHAR(50) NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        )
        """
        self.cursor.execute(create_table)
        self.conn.commit()


    def batch_insert_group_configs(self, config_data: Dict) -> int:
        """
        批量插入group配置记录
    
        Args:
            config_data: 包含groupCode2json和groupCode2name的字典
            
        Returns:
            成功插入的记录数
        """
        # 准备批量插入数据
        batch_data = []
        for group_code, json_file in config_data["groupCode2json"].items():
            group_name = config_data["groupCode2name"].get(group_code, "")
            batch_data.append((group_code, json_file, group_name))
        # 准备批量插入数据
        batch_data = []
        for group_code, json_file in config_data["groupCode2json"].items():
            group_name = config_data["groupCode2name"].get(group_code, "")
            batch_data.append((group_code, json_file, group_name))
        # 使用executemany进行批量插入
        sql = """
        INSERT INTO group_config (group_code, json_file, group_name)
        VALUES (%s, %s, %s)
        ON DUPLICATE KEY UPDATE 
            json_file = VALUES(json_file), 
            group_name = VALUES(group_name)
        """
        self.cursor.executemany(sql, batch_data)
        self.conn.commit()
        return len(batch_data)
    
    def insert_initial_data(self, config_data: Dict) -> None:
        """
        插入初始配置数据
        
        Args:
            config_data: 配置数据字典
        """
        # 确保表已存在
        self.initialize_table()
        
        # 清空现有数据
        self.cursor.execute("TRUNCATE TABLE group_config")
        
        # 插入新数据
        for group_code, json_file in config_data["groupCode2json"].items():
            group_name = config_data["groupCode2name"].get(group_code, "")
            self.add_group_config(group_code, json_file, group_name)
        
        self.conn.commit()
    
    # CRUD操作
    def add_group_config(self, group_code: str, json_file: str, group_name: str) -> int:
        """
        添加group配置记录
        
        Args:
            group_code: 组代码
            json_file: 对应的json文件名
            group_name: 组名称
            
        Returns:
            插入的记录ID
        """
        sql = """
        INSERT INTO group_config (group_code, json_file, group_name)
        VALUES (%s, %s, %s)
        ON DUPLICATE KEY UPDATE json_file = VALUES(json_file), group_name = VALUES(group_name)
        """
        self.cursor.execute(sql, (group_code, json_file, group_name))
        self.conn.commit()
        return self.cursor.lastrowid
    
    def get_group_config(self, group_code: str) -> Optional[Dict]:
        """
        获取单个group配置记录
        
        Args:
            group_code: 组代码
            
        Returns:
            包含group配置信息的字典，如果不存在返回None
        """
        sql = "SELECT * FROM group_config WHERE group_code = %s"
        self.cursor.execute(sql, (group_code,))
        result = self.cursor.fetchone()
        
        if result:
            columns = [col[0] for col in self.cursor.description]
            return dict(zip(columns, result))
        return None
    
    def get_all_group_configs(self) -> List[Dict]:
        """
        获取所有group配置记录
        
        Returns:
            group配置记录列表
        """
        sql = "SELECT * FROM group_config"
        self.cursor.execute(sql)
        results = self.cursor.fetchall()
        
        columns = [col[0] for col in self.cursor.description]
        return [dict(zip(columns, row)) for row in results]
    
    def update_group_config(self, group_code: str, json_file: str = None, group_name: str = None) -> bool:
        """
        更新group配置记录
        
        Args:
            group_code: 要更新的组代码
            json_file: 新的json文件名(可选)
            group_name: 新的组名称(可选)
            
        Returns:
            是否成功更新
        """
        updates = []
        params = []
        
        if json_file:
            updates.append("json_file = %s")
            params.append(json_file)
        
        if group_name:
            updates.append("group_name = %s")
            params.append(group_name)
            
        if not updates:
            return False
            
        sql = f"UPDATE group_config SET {', '.join(updates)} WHERE group_code = %s"
        params.append(group_code)
        
        self.cursor.execute(sql, tuple(params))
        self.conn.commit()
        return self.cursor.rowcount > 0
    
    def add_single_record(self, group_code: str, group_name: str) -> int:
        print(f"Attempting to insert/update group_code: {group_code}")
        # 第一次执行：插入新记录
        single_json_file = f"{group_name}.json"
        result_insert = manager.add_group_config(group_code, single_json_file, group_name)
        if result_insert > 0:
            print(f"Single record insert/update successful for {group_code}. Rowcount: {result_insert}")
            # 验证插入
            inserted_record = manager.get_group_config(group_code)
            print(f"Inserted record: {inserted_record}")
        else:
            print(f"Failed to insert/update single record for {group_code}. Rowcount: {result_insert}")
    


    def delete_group_config(self, group_code: str) -> bool:
        """
        删除group配置记录
        
        Args:
            group_code: 要删除的组代码
            
        Returns:
            是否成功删除
        """
        sql = "DELETE FROM group_config WHERE group_code = %s"
        self.cursor.execute(sql, (group_code,))
        self.conn.commit()
        return self.cursor.rowcount > 0
    
    def close(self) -> None:
        """
        关闭数据库连接
        """
        self.cursor.close()
        self.conn.close()
    
    def __enter__(self):
        return self
    
    def __exit__(self, exc_type, exc_val, exc_tb):
        self.close()

# 使用示例
if __name__ == "__main__":
    # 假设配置文件路径为config.json
    init_config_path = "/opt/wyh/LSP_book_rec/Online_LSP_REC_SYS/configs/config.json"
    db_config_path = "/opt/wyh/LSP_book_rec/Online_LSP_REC_SYS/configs/db_configs.json"
    
    with GroupConfigManager(db_config_path) as manager:
        # 初始化表
        # manager.initialize_table()
        
        # 读取配置文件
        # with open(init_config_path, 'r', encoding="utf-8") as f:
        #     config_data = json.load(f)
        
        # 插入初始数据
        # manager.insert_initial_data(config_data)
        
        # 查询所有group配置
        # print("All group configs:")
        # for config in manager.get_all_group_configs():
        #     print(config)
        
        # 查询单个group
        print("\nGroup 200572.0:")
        print(manager.get_group_config("200572.0"))
        
        # exit()
        # 更新group
        # manager.update_group_config("200370", group_name="WDU")
        # print("\nAfter update:")
        # print(manager.get_group_config("200370"))
        
        # 添加单个记录
        manager.add_single_record("200370", "WDU")

        # # 删除group
        # manager.delete_group_config("000000")
        # print("\nAfter deleting test group:")
        # print("Remaining groups:", len(manager.get_all_group_configs()))
